package com.edu.hre.db.sql.recruit;

import java.io.Serializable;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.edu.hre.common.utils.CommonUtils;
import com.edu.hre.db.pojo.HreCandidateEducation;
import com.edu.hre.db.service.BaseService;
import com.edu.hre.db.utils.Compare;
import com.edu.hre.struts.form.recruit.CanEducationForm;
import com.edu.hre.struts.form.recruit.CanEducationListForm;

public class CanEducationDAOService implements Serializable {
	
	/**
	 * 
	 */
	private static final long serialVersionUID = 3394301416150421253L;
	public static final String BEAN_NAME = "canEducationDAOService";
	private BaseService baseService  = null;
	 
	public void setBaseService(BaseService baseService) {
		this.baseService = baseService;
	} 

	public ArrayList<CanEducationForm> getCanEducationList(int limit, int offset, int canId){

		ArrayList<CanEducationForm> listResult = new ArrayList<CanEducationForm>();

		String sql = "SELECT cedu.id AS col1," +
					" edu.name AS col2," +
					" cedu.institute AS col3," +
					" maj.name AS col4," +
					" cedu.year AS col5, " +
					" cedu.score AS col6, " +
					" cedu.start_date AS col7, " +
					" cedu.end_date AS col8, " +
					" cedu.candidate_id AS col9 " +

					" FROM hre_candidate_education AS cedu, " +
					" hre_education AS edu, " +
					" hre_job_candidate AS can, " +
					" hre_major AS maj " +

					" WHERE cedu.education_id = edu.id " +
					" AND cedu.candidate_id = can.id " +
					" AND cedu.major_id = maj.id " +
					" AND can.is_deleted = 0 " +
					" AND can.id = ? " +
					" ORDER BY cedu.date_created DESC";
		
		List <Object[]> listObject = baseService.listArrayObject(sql, limit, offset, canId);
		for(int i = 0; listObject != null && i < listObject.size(); i++){

			try {

				Object[] objs = listObject.get(i);
				CanEducationForm edu = new CanEducationForm();

				edu.setId(Integer.parseInt(CommonUtils.normalizeObjectToString(objs[1], "0")));
				edu.setCanId(canId);
				edu.setLevel(CommonUtils.normalizeObjectToString(objs[2], ""));
				edu.setInstitute(CommonUtils.normalizeObjectToString(objs[3], ""));
				edu.setMajor(CommonUtils.normalizeObjectToString(objs[4], ""));
				edu.setYear(CommonUtils.normalizeObjectToString(objs[5], ""));
				edu.setScore(CommonUtils.normalizeObjectToString(objs[6], ""));
				
				try {
					edu.setStartDate(CommonUtils.dateToString((Date) objs[7]));
					edu.setEndDate(CommonUtils.dateToString((Date) objs[8]));
				} catch (Exception e) {
					CommonUtils.println(e.getMessage());
				}

				listResult.add(edu);
			} catch (Exception e) {
				CommonUtils.println(e.getMessage());
			}
		}
		
		return listResult;
	}

	public int countCanEducations(int canId){

		int count = baseService.countByColumns(new String[]{"candidateId"}, new int[]{Compare.EQUAL}, new Integer[]{canId}, "id", HreCandidateEducation.class);
		return count < 0 ? 0 : count;
	}

	public void deleteEducations(CanEducationListForm form){

		ArrayList<CanEducationForm> info = (ArrayList<CanEducationForm>) form.getAllEducations();

		for (int i = 0; i < info.size(); i++) {

			try {

				CanEducationForm work = info.get(i);
				if (work.isSelected()) {
					baseService.deleteFirstPojo("id", Compare.EQUAL, work.getId(), null, null, 0, HreCandidateEducation.class);
				}
			} catch (Exception e) {
				CommonUtils.println(e.getMessage());
			}
		}
	}
}
